package com.gua.jbs.utility;

/**
 * 
 * @author Piyush This is for writing the queries to be used in the application
 */
public interface QueryConstant {

	// User related queries
	static final String SAVE_USER = "insert into JBSULT values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:')";
	static final String UPDATE_USER_PASSWORD = "update JBSULT set PASSWORD=':2:' where USER_LOGIN_ID = :0: and PASSWORD = ':1:'";
	static final String DELETE_USER = "update JBSULT set IS_ACTIVE='N' where USER_LOGIN_ID =':0:'";
	static final String ALL_USERS = "SELECT * FROM JBSULT WHERE IS_ACTIVE='Y'";
	static final String UPDATE_USER_ROLE = "update JBSURT set ROLE_ID=':1:' where USER_LOGIN_ID = :0:";
	static final String CHECK_AUTHENTICATION = "select * from JBSULT ult where ult.USER_FNAME = ':0:' and ult.PASSWORD = ':1:'";
	// User related queries

	// Bill related queries
	static final String SAVE_CUST_BILL = "insert into JBSCIT values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:',':12:',':13:',':14:',':15:',':16:',':17:',':18:',':19:')";
	// Bill related queries

	// Stock related queries
	static final String SAVE_PRODUCT_STOCK = "insert into JBSPST values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:',':12:')";
	static final String GET_PRODUCT_LATEST_STOCK = " SELECT PREV_STOCK,CURR_STOCK FROM JBSPST WHERE PROD_ID =':0:' and IS_ACTIVE=':1:' order by CREATED_DT desc";
	static final String GET_PRODUCTS_STOCK = "SELECT pit.PROD_ID as PROD_ID,pit.PROD_NAME as PROD_NAME,pit.PROD_CODE as PROD_CODE,PREV_STOCK,CURR_STOCK FROM JBSPST pst,JBSPIT pit WHERE pit.IS_ACTIVE=':0:' and pst.IS_ACTIVE=':0:' and pit.PROD_ID = pst.PROD_ID order by pst.CREATED_DT desc";
	// Stock related queries

	// Order Related Queries
	static final String SAVE_ORDER_STATE = "insert into JBSOHT values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:')";
	static final String SAVE_ORDER = "insert into JBSOIT values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:',':12:',':13:',':14:',':15:',':16:',':17:',':18:',':19:',':20:',':21:')";
	static final String UPDATE_ORDER_BY_PARENT_ID = "Update JBSOIT set PARENT_ID = ':0:' where ORDER_ID = ':1:'";
	static final String GET_ALL_ACTIVE_ORDERS = "Select cit.CUST_FNAME as CUSTOMER, oit.ORDER_ID as ORDER_ID, oit.TOTAL_PRICE as AMOUNT, oit.PURCHASE_DT as ORDER_DATE from JBSOIT oit, JBSCIT cit where oit.CUST_ID = cit.CUST_ID and oit.IS_ACTIVE = ':0:' and oit.PARENT_ID = ':1:'";
	static final String GET_ORDER_COUNT_PAYMENT_MODE = "select count(*) from JBSOIT where IS_ACTIVE = ':0:' and PAYMENT_MODE like '%:1:%';";
	// Order Related Queries

	// Product Related Queries
	static final String GET_PRODUCT_TYPES = "SELECT * FROM JBSCMT WHERE IS_PARENT=':0:' ORDER BY CATEGORY_NAME";
	static final String GET_PRODUCT_COLOR = "SELECT * FROM JBSCDT WHERE CODE_GROUP=':0:' ORDER BY CODE_NAME";
	static final String GET_PRODUCT_SIZE = "SELECT * FROM JBSCDT WHERE CODE_GROUP=':0:' ORDER BY CODE_NAME";
	static final String GET_PRODUCT_POLISH = "SELECT * FROM JBSCDT WHERE CODE_GROUP=':0:' ORDER BY CODE_NAME";
	// Query for product filter on date Bill
	static final String GET_PRODUCTS_BY_DATE = " SELECT * FROM JBSPIT WHERE CREATED_DT BETWEEN ':0:' AND ':1:'";
	static final String SAVE_PRODUCT = "insert into JBSPIT values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:',':12:',':13:',':14:',':15:',':16:',':17:', ':18:', ':19:', ':20:', ':21:')";
	static final String ALL_PRODUCT = "select distinct(pit.PROD_NAME) as PROD_NAME,pit.PROD_ID as ID, cdt.CODE_NAME as SIZE,cdt1.CODE_NAME as COLOR,cmt.CATEGORY_NAME as TYPE,cmt1.CATEGORY_NAME as SUB_TYPE,cdt2.CODE_NAME as POLISH,pit.PROD_PRICE as PRICE,pit.PROD_COST as COST, pit.HR_CODE as HR_CODE,pit.PROD_CODE as BAR_CODE, pit.DESIGN_CODE as DESIGN_CODE, pit.PROD_IMAGE as IMAGE, pit.EXTRA_FIELD1 as DESCRIPTION, pit.EXTRA_FIELD3 as STOCK, pit.IS_APPROVED as APPROVAL, pit.CREATED_DT as CREATED_DT from JBSPIT as pit, JBSCDT as cdt,JBSCMT as cmt,JBSCMT as cmt1, JBSCDT as cdt1, JBSCDT as cdt2, JBSCDT as cdt3,JBSCDT as cdt4 where pit.PROD_SIZE=cdt.CODE_ID and pit.PROD_COLOR = cdt1.CODE_ID and pit.PROD_TYPE = cmt.CATEGORY_MASTER_ID and pit.PROD_SUB_TYPE = cmt1.CATEGORY_MASTER_ID and pit.EXTRA_FIELD2 = cdt2.CODE_ID and pit.IS_ACTIVE = 'Y'";
	static final String GET_PRODUCT_FOR_ID = "select distinct(pit.PROD_NAME) as PROD_NAME,pit.PROD_ID as ID, cdt.CODE_NAME as SIZE,cdt1.CODE_NAME as COLOR,cmt.CATEGORY_NAME as TYPE,cmt1.CATEGORY_NAME as SUB_TYPE,cdt2.CODE_NAME as POLISH,pit.PROD_PRICE as PRICE, pit.PROD_COST as COST, pit.HR_CODE as HR_CODE,pit.PROD_CODE as BAR_CODE, pit.DESIGN_CODE as DESIGN_CODE, pit.PROD_IMAGE as IMAGE, pit.EXTRA_FIELD1 as DESCRIPTION, pit.EXTRA_FIELD3 as STOCK, pit.IS_APPROVED as APPROVAL, pit.CREATED_DT as CREATED_DT from JBSPIT as pit, JBSCDT as cdt,JBSCMT as cmt,JBSCMT as cmt1, JBSCDT as cdt1, JBSCDT as cdt2, JBSCDT as cdt3,JBSCDT as cdt4 where pit.PROD_SIZE=cdt.CODE_ID and pit.PROD_COLOR = cdt1.CODE_ID and pit.PROD_TYPE = cmt.CATEGORY_MASTER_ID and pit.PROD_SUB_TYPE = cmt1.CATEGORY_MASTER_ID and pit.EXTRA_FIELD2 = cdt2.CODE_ID and pit.IS_ACTIVE = 'Y' and  pit.PROD_ID = ':0:'";
	static final String PRODUCT_ON_ID = "select * from JBSPIT where PROD_ID =':0:'";
	static final String UPDATE_PRODUCT_ON_ID = "update JBSPIT set PROD_NAME=':1:',PROD_COLOR=':2:',PROD_SIZE=':3:',PROD_WEIGHT=':4:',PROD_TYPE=':5:',PROD_IMAGE=':6:',"
			+ " IS_APPROVED=':7:',IS_ACTIVE=':8:',MODIFY_DT=':9:',EXTRA_FIELD1=':10:',EXTRA_FIELD2=':11:',EXTRA_FIELD3=':12:',CREATED_BY=':13:',MODIFIED_BY=':14:',PROD_PRICE =':15:', DESIGN_CODE=':16:', HR_CODE=':17:',PROD_SUB_TYPE =':18:', PROD_COST =':19:' where PROD_ID =':0:'";
	static final String deleteProduct = "update JBSPIT set IS_ACTIVE='N' where PROD_ID =':0:'";
	static final String GET_PRODUCT_BY_CODE = "select * from JBSPIT where PROD_CODE =':0:' and IS_ACTIVE=':1:' ";
	static final String GET_PRODUCT_BY_ID = "select pit.EXTRA_FIELD3 as STOCK from JBSPIT as pit where PROD_ID =':0:' and IS_ACTIVE=':1:' ";
	// Product Related Queries

	// Invoice Related Queries
	static final String GET_INVOICE_NO = "SELECT * FROM JBSCDT WHERE CODE_GROUP=':0:'";
	static final String UPDATE_INVOICE_NO = "UPDATE JBSCDT SET CODE_VALUE=':0:' WHERE CODE_NAME = ':1:'";
	// Invoice Related Queries

	// Coupon Related queries
	static final String GET_COUPON_TYPES = "SELECT * FROM JBSCDT WHERE CODE_GROUP=':0:' ORDER BY CODE_NAME";
	static final String GET_COUPON_BY_CODE = "SELECT * FROM JBSCDT WHERE CODE_GROUP=':0:' AND CODE_NAME=':1:'";
	static final String SAVE_COUPON = "insert into JBSCDT values(':0:',':1:',':2:',':3:',':4:',':5:',':6:',':7:',':8:',':9:',':10:',':11:')";
	// Coupon Related queries

	// Bar Code Related Queries
	static final String GET_BAR_CODE_COUNT_VALUE = "SELECT CODE_VALUE FROM JBSCDT WHERE CODE_NAME = ':0:'";
	static final String UPDATE_BAR_CODE_COUNT_VALUE = "UPDATE JBSCDT SET CODE_VALUE=':0:' WHERE CODE_NAME = ':1:'";
	// Bar Code Related Queries

	// Other queries
	static final String GET_PRIMARY_KEY = "select MAX(':0:') AS maxId FROM ':1:'";

}
